package sg.edu.nus.iss.cats.dataaccess.mysql;

import java.sql.*;
import java.util.*;
import sg.edu.nus.iss.cats.dataaccess.*;

public class SQLObjectManager {

    static {
        try {
            Class.forName(DBConstants.driverName).newInstance();
        } catch (ClassNotFoundException c){
        } catch (InstantiationException i){
        } catch (IllegalAccessException i){
        }
    }

    private ObjectHandler handler;
    private String whereClause = null;

    public SQLObjectManager (ObjectHandler handler) {
        this.handler = handler;
    }

    public void setQueryConstraint (String whereClause) {
        this.whereClause = whereClause;
    }

    protected String getSelectQuery () {
        String query = "SELECT * FROM " + handler.getTableName ();
        if (whereClause != null) {
            query += " WHERE " + whereClause;
        }
        return query;
    }

    protected Object getObject () throws DAOException {
        Object[] results = getObjects ();
        if (results.length == 0) {
            return null;
        }
        return (results[0]);
    }

    protected Object[] getObjects () throws DAOException {
        Connection conn = null;
        Vector v = new Vector();
        try {
            conn = DriverManager.getConnection(DBConstants.dbUrl,
                DBConstants.dbUsername, 
                DBConstants.dbPassword);
            System.out.println("Created Connection");

            Statement statement = conn.createStatement();
            String sql = getSelectQuery ();
            System.out.println("Executing query: " + sql);

            ResultSet results = statement.executeQuery(sql);
            System.out.println("Executed query");

            // Go through the results- there must be at the most one.
            while (results.next()) {
                System.out.println("Processing row");
                v.add (handler.retrieveObject (results));
            }
            results.close();
            statement.close ();
            System.out.println("Retrieved objects- number found: " + v.size());

        } catch (Exception e) {

            // If there is an error, we throw an exception.
            String error = "Error retrieving object. Nested Exception is: " + e;
            System.out.println (error);
            throw new DAOException (error);

        } finally {
            try {
                System.out.println("Closing connection");
                conn.close();
            } catch (Exception e) {}
        }

        Object[] result = handler.getObjectArray (v.size());

        try {
            for (int i = 0; i < v.size(); i++) {
                Object obj = v.get(i);
                handler.completeObjectRetrieve (obj);
                result [i] = obj;
            }
        } catch (DAOException se) {
            // If there is an error, we throw an exception.
            String error = "Error completing object. Nested Exception is: " + se;
            System.out.println (error);
            throw new DAOException (error);

        }
        return (result);
    }

    protected void updateObject (Object obj)
                    throws DAOException {
        updateObjects (new Object[] {obj});
    }

    protected void updateObjects (Object[] objs)
                    throws DAOException {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(DBConstants.dbUrl,
                DBConstants.dbUsername, 
                DBConstants.dbPassword);
            System.out.println("Created Connection");

            this.setQueryConstraint (handler.getKeyParameterConstraint());
            String sql = getSelectQuery ();
            PreparedStatement statement = conn.prepareStatement(sql,
                ResultSet.TYPE_SCROLL_SENSITIVE,
                ResultSet.CONCUR_UPDATABLE);
            for (int i = 0; i < objs.length; i++) {
                handler.setKeyParameters (statement, objs[i]);
                ResultSet results = statement.executeQuery();
                results.beforeFirst();
                do {
                    results.next();
                    handler.updateObject (results, objs[i]);
                    results.updateRow ();
                } while (!results.isLast());
                results.close();
            }
            statement.close ();
            System.out.println("Modified objects");

        } catch (Exception e) {

            // If there is an error, we throw an exception.
            String error = "Error modifying object. Nested Exception is: " + e;
            System.out.println (error);
            throw new DAOException (error);

        } finally {
            try {
                System.out.println("Closing connection");
                conn.close();
            } catch (Exception e) {}
        }
    }

    protected void createObject (Object obj)
                    throws DAOException {
        createObjects (new Object[] {obj});
    }

    protected void createObjects (Object[] objs)
                    throws DAOException {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(DBConstants.dbUrl,
                DBConstants.dbUsername, 
                DBConstants.dbPassword);
            System.out.println("Created Connection");

            String sql = getSelectQuery ();
            Statement statement = conn.createStatement(
                ResultSet.TYPE_SCROLL_SENSITIVE,
                ResultSet.CONCUR_UPDATABLE);
            ResultSet results = statement.executeQuery(sql);
            results.moveToInsertRow();
            for (int i = 0; i < objs.length; i++) {
                handler.insertKeys (results, objs[i]);
                handler.updateObject (results, objs[i]);
                results.insertRow ();
            }
            results.close();
            statement.close ();
            System.out.println("Created objects");

        } catch (Exception e) {

            // If there is an error, we throw an exception.
            String error = "Error creating object. Nested Exception is: " + e;
            System.out.println (error);
            throw new DAOException (error);

        } finally {
            try {
                System.out.println("Closing connection");
                conn.close();
            } catch (Exception e) {}
        }
    }
}